<?php

class Report extends CModel
{
	static $schema = array(
		'nap_tien_theo_httt' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Hình thức thanh toán',
				'field' => 'payment',
			),
			array(
				'label' => 'Giá trị',
				'field' => 'total_amount',
				'format' => 'price',
			),
		),

		'nap_tien_theo_kh' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Họ tên khách hàng',
				'field' => 'name',
			),
			array(
				'label' => 'Email',
				'field' => 'email',
			),
			array(
				'label' => 'Tổng số tiền đã nạp',
				'field' => 'total_amount',
				'format' => 'price',
			),
			array(
				'label' => 'Số lần nạp',
				'field' => 'total_trans',
				'format' => 'number',
			),
		),

		'chi_tiet_nap_tien' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Họ tên khách hàng',
				'field' => 'name',
			),
			array(
				'label' => 'Email',
				'field' => 'email',
			),
			array(
				'label' => 'Hình thức nạp tiền',
				'field' => 'payment',
			),
			array(
				'label' => 'Ngày nạp',
				'field' => 'timestamp',
				'format' => 'datetime',
			),
			array(
				'label' => 'Giá trị',
				'field' => 'amount',
				'format' => 'price',
			),
		),

		'download_theo_ebook' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Mã eBook',
				'field' => 'product_code',
			),
			array(
				'label' => 'Tên eBook',
				'field' => 'name',
			),
			array(
				'label' => 'Tên NCC (CP)',
				'field' => 'cp_name',
				'sortable' => true,
			),
			array(
				'label' => 'Số lượng',
				'field' => 'so_luong',
				'format' => 'number',
				'sortable' => true,
			),
			array(
				'label' => 'Giá trị',
				'field' => 'total_amount',
				'format' => 'price',
				'sortable' => true,
			),
		),

		'download_theo_topic' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Chủ đề',
				'field' => 'category',
			),
			array(
				'label' => 'Số lượng',
				'field' => 'so_luong',
				'format' => 'number',
				'sortable' => true,
			),
			array(
				'label' => 'Giá trị',
				'field' => 'total_amount',
				'format' => 'price',
				'sortable' => true,
			),
		),

		'download_theo_cp' => array(
			array(
				'label' => 'STT',
				'auto_increment' => true,
			),
			array(
				'label' => 'Tên NCC (CP)',
				'field' => 'cp_name',
			),
			array(
				'label' => 'Số lượng',
				'field' => 'so_luong',
				'format' => 'number',
				'sortable' => true,
			),
			array(
				'label' => 'Giá trị',
				'field' => 'total_amount',
				'format' => 'price',
				'sortable' => true,
			),
		),
	);


	public function attributeNames() {
		return array();
	}

	public static function getSchema($report) {
		if ( isset(self::$schema[$report]) ) {
			return self::$schema[$report];
		}
	}

	public static function get($report, &$filters, $order = 'timestamp', $orderby = 'desc') {
		$reports = array();

		if ( method_exists('Report', $report) ) {
			$reports = call_user_func('Report::'.$report, &$filters, $order, $orderby);
		}

		return $reports;
	}

	public static function nap_tien_theo_httt(&$filters, $order = 'timestamp', $orderby = 'desc') {

		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$command = app()->db->createCommand()
			->select('t.payment_id, p.payment, SUM(t.amount) AS total_amount')
			->from('{{transactions}} t')
			->join('{{payments}} p', 't.payment_id = p.payment_id')
			->group('t.payment_id')
			->order('total_amount desc');

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}

	public static function nap_tien_theo_kh(&$filters, $order = 'timestamp', $orderby = 'desc') {
		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$command = app()->db->createCommand()
			->select('t.payment_id, SUM(t.amount) AS total_amount, COUNT(t.payment_id) AS total_trans, u.user_login, u.email, u.name')
			->from('{{transactions}} t')
			->join('{{users}} u', 't.user_id = u.user_id')
			->group('t.user_id')
			->order('total_amount desc');

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}

	public static function chi_tiet_nap_tien(&$filters, $order = 'timestamp', $orderby = 'desc') {
		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$command = app()->db->createCommand()
			->select('t.payment_id, t.timestamp, t.amount, u.user_login, u.email, u.name, p.payment, DATE_FORMAT(FROM_UNIXTIME(t.timestamp), "%d-%m-%Y %H:%i") AS datetime')
			->from('{{transactions}} t')
			->join('{{payments}} p', 't.payment_id = p.payment_id')
			->join('{{users}} u', 't.user_id = u.user_id')
			->order('t.timestamp desc');

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}

	public static function download_theo_ebook(&$filters, $order = 'timestamp', $orderby = 'desc') {
		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$sortings = array(
			'timestamp' => 't.timestamp',
			'so_luong' => 'COUNT(od.product_id)',
			'total_amount' => 'SUM(od.price)',
			'cp_name' => 'cp_name',
		);

		if ( isset($sortings[$order]) ) {
			$order = $sortings[$order];
		} else {
			$order = $sortings['total_amount'];
		}

		$command = app()->db->createCommand()
			->select('p.product_code, p.product_id, pd.name, IF(u.name IS NOT NULL, u.name, "(empty)") AS cp_name, COUNT(od.product_id) AS so_luong, SUM(od.price) AS total_amount')
			->from('{{orders}} t')
			->join('{{order_details}} od', 't.order_id = od.order_id')
			->join('{{products}} p', 'od.product_id = p.product_id')
			->join('{{product_descriptions}} pd', 'p.product_id = pd.product_id')
			->leftJoin('{{products_cps}} c', 'p.product_id = c.product_id')
			->leftJoin('{{users}} u', 'u.user_id = c.user_id')
			->group('od.product_id')
			->order("$order $orderby");

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}
	public static function download_theo_topic(&$filters, $order = 'timestamp', $orderby = 'desc') {
		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$sortings = array(
			'timestamp' => 't.timestamp',
			'so_luong' => 'COUNT(od.product_id)',
			'total_amount' => 'SUM(od.price)',
		);

		if ( isset($sortings[$order]) ) {
			$order = $sortings[$order];
		} else {
			$order = $sortings['total_amount'];
		}


		$command = app()->db->createCommand()
			->select('c.category_id, IF(c.category IS NOT NULL, c.category, "(deleted)") AS category, COUNT(od.product_id) AS so_luong, SUM(od.price) AS total_amount')
			->from('{{orders}} t')
			->join('{{order_details}} od', 't.order_id = od.order_id')
			->join('{{products}} p', 'od.product_id = p.product_id')
			->join('{{product_categories}} pc', 'p.product_id = pc.product_id AND pc.type = "M"')
			->leftJoin('{{categories}} c', 'pc.category_id = c.category_id')
			->group('c.category_id')
			->order("$order $orderby");

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}
	public static function download_theo_cp(&$filters, $order = 'timestamp', $orderby = 'desc') {
		$conditions = "t.status=:status";
		$params = array(
			':status' => 'A',
		);

		$sortings = array(
			'timestamp' => 't.timestamp',
			'so_luong' => 'COUNT(od.product_id)',
			'total_amount' => 'SUM(od.price)',
		);

		if ( isset($sortings[$order]) ) {
			$order = $sortings[$order];
		} else {
			$order = $sortings['total_amount'];
		}

		$command = app()->db->createCommand()
			->select('IF(u.name IS NOT NULL, u.name, "(empty)") AS cp_name, COUNT(od.product_id) AS so_luong, SUM(od.price) AS total_amount')
			->from('{{orders}} t')
			->join('{{order_details}} od', 't.order_id = od.order_id')
			->join('{{products}} p', 'od.product_id = p.product_id')
			->join('{{product_descriptions}} pd', 'p.product_id = pd.product_id')
			->leftJoin('{{products_cps}} c', 'p.product_id = c.product_id')
			->leftJoin('{{users}} u', 'u.user_id = c.user_id')
			->group('u.user_id')
			->order("$order $orderby");

		if ( !empty($filters['period']) || !empty($filters['time_from']) || !empty($filters['time_to']) ) {
			list($time_from, $time_to) = createPeriods($filters);

			$conditions .= " AND (t.timestamp > :time_from AND t.timestamp < :time_to)";
			$params[':time_from'] = $time_from;
			$params[':time_to'] = $time_to;

			$filters['time_from'] = formatDate($time_from);
			$filters['time_to'] = formatDate($time_to);
		}

		$command->where($conditions, $params);

		$reports = $command->queryAll();

		return $reports;
	}

}